-- This script has to do its own schema to make sure the trigger works.
CREATE SCHEMA "Library Makerspace";


CREATE FUNCTION "Library Makerspace".check_training() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Check if the patron is trained for the equipment
    IF NOT EXISTS (
        SELECT 1
        FROM "Library Makerspace"."Equipment Training"
        WHERE "Library Makerspace"."Equipment Training".patron_id = NEW.patron_id
          AND "Library Makerspace"."Equipment Training".equipment_id = NEW.equipment_id
    ) THEN
        RAISE EXCEPTION 'Patron % is not trained on equipment %', NEW.patron_id, NEW.equipment_id;
    END IF;
    RETURN NEW;
END;
$$;


CREATE TABLE "Library Makerspace"."Equipment" (
    id integer NOT NULL,
    name text NOT NULL,
    location text,
    status_id integer,
    type_id integer
);


CREATE TABLE "Library Makerspace"."Equipment Statuses" (
    id integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE "Library Makerspace"."Equipment Statuses" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Equipment Statuses_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


CREATE TABLE "Library Makerspace"."Equipment Training" (
    id integer NOT NULL,
    patron_id integer,
    equipment_id integer,
    trained_at timestamp without time zone DEFAULT now()
);


ALTER TABLE "Library Makerspace"."Equipment Training" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Equipment Training_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


CREATE TABLE "Library Makerspace"."Equipment Types" (
    id integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE "Library Makerspace"."Equipment Types" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Equipment Types_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


ALTER TABLE "Library Makerspace"."Equipment" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Equipment_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


CREATE TABLE "Library Makerspace"."Job Statuses" (
    id integer NOT NULL,
    name text NOT NULL
);


ALTER TABLE "Library Makerspace"."Job Statuses" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Job Statuses_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


CREATE TABLE "Library Makerspace"."Jobs" (
    id integer NOT NULL,
    equipment_id integer,
    patron_id integer,
    status_id integer,
    queue_order integer NOT NULL,
    requested_at timestamp without time zone DEFAULT now(),
    job_start timestamp without time zone,
    job_end timestamp without time zone
);


ALTER TABLE "Library Makerspace"."Jobs" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Jobs_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


CREATE TABLE "Library Makerspace"."Patrons" (
    id integer NOT NULL,
    name text NOT NULL,
    email mathesar_types.email NOT NULL
);


ALTER TABLE "Library Makerspace"."Patrons" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME "Library Makerspace"."Patrons_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


INSERT INTO "Library Makerspace"."Equipment" VALUES
  (1, 'Sawgrass Sublimation Printer W987', 'Main Library', 4, 4),
  (2, 'MakerBot 3D Printer D224', 'Mount Pleasant', 4, 5),
  (3, 'Singer Sewing Machine N364', 'Main Library', 3, 1),
  (4, 'Cricut Vinyl Cutter S798', 'Mount Pleasant', 2, 3),
  (5, 'Epilog Laser Cutter N416', 'Main Library', 4, 2),
  (6, 'Epilog Laser Cutter F650', 'Rochambeau', 1, 2),
  (7, 'Cricut Vinyl Cutter J407', 'Main Library', 2, 3),
  (8, 'Brother Sewing Machine F892', 'Rochambeau', 1, 1),
  (9, 'MakerBot 3D Printer V670', 'Mount Pleasant', 2, 5),
  (10, 'Janome Sewing Machine C625', 'Mount Pleasant', 4, 1),
  (11, 'Canon Sublimation Printer H926', 'Main Library', 2, 4),
  (12, 'Creality 3D Printer J991', 'Knight Memorial', 4, 5),
  (13, 'MakerBot 3D Printer J805', 'Rochambeau', 4, 5),
  (14, 'Cricut Vinyl Cutter Q537', 'Main Library', 1, 3),
  (15, 'Glowforge Laser Cutter I191', 'Mount Pleasant', 3, 2),
  (16, 'Cricut Vinyl Cutter R145', 'Main Library', 1, 3),
  (17, 'Cricut Vinyl Cutter S184', 'Mount Pleasant', 4, 3),
  (18, 'Epilog Laser Cutter T355', 'Mount Pleasant', 1, 2),
  (19, 'Cricut Vinyl Cutter D133', 'Main Library', 4, 3),
  (20, 'Prusa 3D Printer G693', 'Knight Memorial', 2, 5),
  (21, 'Janome Sewing Machine X183', 'Main Library', 2, 1),
  (22, 'Epson Sublimation Printer G314', 'Main Library', 3, 4),
  (23, 'Silhouette Vinyl Cutter H407', 'Main Library', 4, 3),
  (24, 'Brother Sewing Machine T514', 'Rochambeau', 2, 1),
  (25, 'Brother Sewing Machine O206', 'Rochambeau', 1, 1),
  (26, 'Silhouette Vinyl Cutter C590', 'Mount Pleasant', 2, 3),
  (27, 'Creality 3D Printer V252', 'Main Library', 3, 5),
  (28, 'Janome Sewing Machine Y500', 'Knight Memorial', 1, 1),
  (29, 'Creality 3D Printer E740', 'Mount Pleasant', 2, 5),
  (30, 'Epson Sublimation Printer X133', 'Main Library', 3, 4);


INSERT INTO "Library Makerspace"."Equipment Statuses" VALUES
  (1, 'Available'),
  (2, 'In Use'),
  (3, 'Out of Order'),
  (4, 'Under Maintenance'),
  (5, 'On Loan');


INSERT INTO "Library Makerspace"."Equipment Training" VALUES
  (1, 1, 15, '2025-01-03 23:26:40.199358'),
  (2, 13, 20, '2025-01-06 00:37:45.785448'),
  (3, 12, 24, '2025-01-10 10:02:55.453621'),
  (4, 3, 10, '2025-01-05 17:32:06.870889'),
  (5, 7, 13, '2025-01-03 11:59:26.861895'),
  (6, 1, 25, '2025-01-09 23:53:53.998417'),
  (7, 16, 12, '2025-01-01 10:23:42.33015'),
  (8, 5, 19, '2025-01-10 18:30:43.93464'),
  (9, 14, 1, '2025-01-06 03:51:29.090905'),
  (10, 17, 22, '2025-01-05 23:00:01.373499'),
  (11, 20, 27, '2025-01-14 18:13:08.864392'),
  (12, 17, 7, '2025-01-14 05:05:37.29308'),
  (13, 1, 23, '2025-01-11 02:51:56.943888'),
  (14, 8, 4, '2025-01-13 02:43:22.228214'),
  (15, 14, 30, '2025-01-07 15:32:08.082597'),
  (16, 15, 8, '2025-01-04 05:27:00.87401'),
  (17, 6, 9, '2025-01-10 07:01:43.345248'),
  (18, 16, 19, '2025-01-01 05:50:44.063754'),
  (19, 7, 9, '2025-01-06 21:37:49.904934'),
  (20, 6, 15, '2025-01-03 15:11:16.696003'),
  (21, 12, 21, '2025-01-03 20:22:13.194737'),
  (22, 9, 10, '2025-01-15 08:55:10.341485'),
  (23, 6, 25, '2025-01-10 09:56:28.768175'),
  (24, 6, 21, '2025-01-11 18:52:05.069437'),
  (25, 17, 4, '2025-01-05 11:19:51.536982'),
  (26, 14, 17, '2025-01-16 01:27:04.924575'),
  (27, 8, 1, '2025-01-01 23:59:13.566663'),
  (28, 20, 18, '2025-01-08 18:40:08.225922'),
  (29, 14, 6, '2025-01-13 02:34:10.492475'),
  (30, 10, 15, '2025-01-15 04:27:13.097361'),
  (31, 6, 16, '2025-01-01 18:48:31.680527'),
  (32, 3, 13, '2025-01-13 11:11:24.931279'),
  (33, 20, 11, '2025-01-01 19:53:42.656989'),
  (34, 14, 19, '2025-01-08 22:09:30.93628'),
  (35, 2, 2, '2025-01-01 20:38:42.682912'),
  (36, 2, 26, '2025-01-10 11:54:24.64724'),
  (37, 12, 18, '2025-01-02 13:48:54.654248'),
  (38, 15, 3, '2025-01-05 02:24:30.3631');


INSERT INTO "Library Makerspace"."Equipment Types" VALUES
  (1, 'Sewing Machine'),
  (2, 'Laser Cutter'),
  (3, 'Vinyl Cutter'),
  (4, 'Sublimation Printer'),
  (5, '3D Printer');


INSERT INTO "Library Makerspace"."Job Statuses" VALUES
  (1, 'Pending'),
  (2, 'In Progress'),
  (3, 'Completed'),
  (4, 'Cancelled'),
  (5, 'On Hold'),
  (6, 'See Desk');


INSERT INTO "Library Makerspace"."Jobs" VALUES
  (1, 4, 8, 1, 1, '2025-01-15 19:48:43.552434', '2025-01-02 19:21:02.163269', '2025-01-03 04:48:09.336896'),
  (2, 19, 5, 5, 2, '2025-01-13 00:17:52.839903', NULL, '2025-01-12 12:49:37.435127'),
  (3, 23, 1, 2, 3, '2025-01-06 05:32:06.767621', NULL, NULL),
  (4, 30, 14, 4, 4, '2025-01-14 01:26:41.86686', '2025-01-12 09:18:41.420974', '2025-01-16 03:06:20.292391'),
  (5, 15, 10, 2, 5, '2025-01-12 10:07:31.349304', NULL, '2025-01-10 18:21:18.00838'),
  (6, 3, 15, 1, 6, '2025-01-15 06:53:33.834182', '2025-01-05 22:51:36.625056', NULL),
  (7, 2, 2, 4, 7, '2025-01-08 14:31:47.168803', '2025-01-11 12:42:28.915767', NULL),
  (8, 4, 8, 4, 8, '2025-01-04 07:37:02.303042', NULL, NULL),
  (9, 15, 10, 2, 9, '2025-01-08 09:28:38.631279', NULL, '2025-01-10 09:55:59.987533'),
  (10, 26, 2, 3, 10, '2025-01-14 16:29:21.74173', '2025-01-11 02:02:59.032064', NULL),
  (11, 4, 8, 5, 11, '2025-01-01 19:41:13.006415', '2025-01-15 22:35:54.650185', '2025-01-06 05:26:27.725281'),
  (12, 30, 14, 1, 12, '2025-01-11 22:48:31.286979', '2025-01-04 01:45:30.821146', NULL),
  (13, 19, 5, 2, 13, '2025-01-05 18:14:09.834708', '2025-01-01 08:21:49.295401', '2025-01-12 15:31:48.839205'),
  (14, 19, 5, 1, 14, '2025-01-03 23:27:32.723922', '2025-01-12 22:54:15.106789', '2025-01-08 21:24:31.412678'),
  (15, 12, 16, 4, 15, '2025-01-11 08:05:05.6447', '2025-01-09 03:02:06.710709', '2025-01-15 07:45:53.251518'),
  (16, 10, 9, 1, 16, '2025-01-03 15:01:56.444147', '2025-01-05 11:02:23.259763', NULL),
  (17, 23, 1, 3, 17, '2025-01-15 08:58:16.058669', '2025-01-05 19:17:24.303866', NULL),
  (18, 11, 20, 4, 18, '2025-01-13 07:37:52.066643', '2025-01-11 16:46:46.950879', '2025-01-06 22:59:41.216824'),
  (19, 9, 6, 2, 19, '2025-01-16 13:35:48.445005', '2025-01-01 00:39:17.821871', NULL),
  (20, 6, 14, 1, 20, '2025-01-10 19:35:26.122582', NULL, NULL),
  (21, 4, 17, 5, 21, '2025-01-12 07:19:35.253678', NULL, '2025-01-05 06:38:11.32838'),
  (22, 15, 10, 1, 22, '2025-01-09 20:42:41.9677', NULL, NULL),
  (23, 21, 12, 5, 23, '2025-01-14 09:03:28.248323', '2025-01-01 23:56:17.826654', NULL),
  (24, 13, 3, 3, 24, '2025-01-09 21:36:48.511732', NULL, '2025-01-14 17:26:57.553673'),
  (25, 19, 14, 2, 25, '2025-01-15 02:39:47.890399', '2025-01-03 21:24:25.330063', NULL),
  (26, 2, 2, 1, 26, '2025-01-12 08:45:31.56859', NULL, NULL),
  (27, 25, 1, 1, 27, '2025-01-05 06:25:57.856893', '2025-01-04 13:45:29.698746', '2025-01-03 02:25:12.923054'),
  (28, 19, 16, 2, 28, '2025-01-11 06:51:18.877261', '2025-01-04 10:29:33.974414', NULL),
  (29, 26, 2, 1, 29, '2025-01-12 13:20:50.112684', '2025-01-16 18:41:40.43977', NULL),
  (30, 19, 16, 4, 30, '2025-01-09 02:15:02.549183', '2025-01-07 15:24:35.925649', '2025-01-09 00:23:02.284361'),
  (31, 15, 6, 4, 31, '2025-01-14 04:25:22.753648', NULL, NULL),
  (32, 6, 14, 1, 32, '2025-01-15 00:12:46.609896', '2025-01-07 12:34:32.447647', NULL),
  (33, 25, 6, 1, 33, '2025-01-10 06:10:45.086229', '2025-01-02 04:41:57.962266', NULL),
  (34, 15, 1, 5, 34, '2025-01-02 12:17:25.561854', NULL, '2025-01-15 08:14:34.964554'),
  (35, 2, 2, 4, 35, '2025-01-16 08:56:05.594327', NULL, NULL),
  (36, 30, 14, 4, 36, '2025-01-03 22:43:17.443394', '2025-01-08 18:15:33.932495', NULL),
  (37, 27, 20, 3, 37, '2025-01-02 08:23:00.759751', NULL, NULL),
  (38, 20, 13, 3, 38, '2025-01-08 08:04:53.197479', NULL, '2025-01-13 14:11:52.436438'),
  (39, 22, 17, 4, 39, '2025-01-05 14:56:29.796596', NULL, '2025-01-11 07:30:21.558733'),
  (40, 11, 20, 2, 40, '2025-01-15 06:38:18.053659', '2025-01-03 07:37:42.236742', '2025-01-10 03:16:06.79425'),
  (41, 8, 15, 2, 41, '2025-01-01 14:57:41.047105', NULL, '2025-01-02 21:43:02.817504'),
  (42, 12, 16, 2, 42, '2025-01-10 16:02:23.584153', NULL, NULL),
  (43, 4, 8, 3, 43, '2025-01-15 13:12:20.947076', '2025-01-02 05:57:52.754697', NULL),
  (44, 25, 1, 4, 44, '2025-01-13 09:04:19.548939', '2025-01-07 12:43:46.636346', NULL),
  (45, 13, 3, 3, 45, '2025-01-15 04:38:52.617357', '2025-01-15 05:13:20.018702', NULL),
  (46, 19, 16, 5, 46, '2025-01-08 16:12:38.108931', '2025-01-12 23:06:25.27885', '2025-01-01 06:26:39.406554'),
  (47, 10, 9, 5, 47, '2025-01-10 08:36:06.709483', '2025-01-11 23:05:11.542603', '2025-01-06 02:24:45.792833'),
  (48, 11, 20, 4, 48, '2025-01-10 07:30:13.04427', '2025-01-13 17:25:34.337202', NULL),
  (49, 13, 7, 5, 49, '2025-01-05 07:02:38.064458', NULL, NULL),
  (50, 1, 14, 1, 50, '2025-01-11 05:02:01.25155', '2025-01-02 16:33:45.935645', '2025-01-10 22:14:47.580793');


INSERT INTO "Library Makerspace"."Patrons" VALUES
  (1, 'John Perkins', 'larrygray@example.org'),
  (2, 'Michael Rodriguez', 'raymondrandall@example.net'),
  (3, 'Rhonda Chung', 'groberts@example.net'),
  (4, 'Curtis Hansen', 'lindabarnett@example.net'),
  (5, 'Stephen Brown', 'campbellclarence@example.net'),
  (6, 'Michael Small', 'debramoon@example.com'),
  (7, 'Maureen Moore', 'chad70@example.net'),
  (8, 'Allison Caldwell', 'howardmelissa@example.net'),
  (9, 'Amanda Mills', 'vprince@example.net'),
  (10, 'Wesley Warren', 'ifisher@example.net'),
  (11, 'Wendy Garza', 'josephcampbell@example.com'),
  (12, 'Ryan Serrano', 'shannon59@example.com'),
  (13, 'Katie Ortega', 'joyce15@example.com'),
  (14, 'Natalie Bryant', 'wendymoore@example.org'),
  (15, 'Sandra Avery', 'brandonhoward@example.net'),
  (16, 'Jennifer Williams', 'daniel17@example.net'),
  (17, 'Nicole Jenkins', 'bgarza@example.net'),
  (18, 'Alexandra Thompson', 'maryclayton@example.org'),
  (19, 'Thomas Burke', 'hhines@example.com'),
  (20, 'Wendy Good', 'fmiller@example.org');


SELECT pg_catalog.setval('"Library Makerspace"."Equipment Statuses_id_seq"', 5, true);
SELECT pg_catalog.setval('"Library Makerspace"."Equipment Training_id_seq"', 38, true);
SELECT pg_catalog.setval('"Library Makerspace"."Equipment Types_id_seq"', 5, true);
SELECT pg_catalog.setval('"Library Makerspace"."Equipment_id_seq"', 30, true);
SELECT pg_catalog.setval('"Library Makerspace"."Job Statuses_id_seq"', 6, true);
SELECT pg_catalog.setval('"Library Makerspace"."Jobs_id_seq"', 50, true);
SELECT pg_catalog.setval('"Library Makerspace"."Patrons_id_seq"', 20, true);


ALTER TABLE ONLY "Library Makerspace"."Equipment Statuses"
    ADD CONSTRAINT "Equipment Statuses_name_key" UNIQUE (name);


ALTER TABLE ONLY "Library Makerspace"."Equipment Statuses"
    ADD CONSTRAINT "Equipment Statuses_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Equipment Training"
    ADD CONSTRAINT "Equipment Training_patron_id_equipment_id_key" UNIQUE (patron_id, equipment_id);


ALTER TABLE ONLY "Library Makerspace"."Equipment Training"
    ADD CONSTRAINT "Equipment Training_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Equipment Types"
    ADD CONSTRAINT "Equipment Types_name_key" UNIQUE (name);


ALTER TABLE ONLY "Library Makerspace"."Equipment Types"
    ADD CONSTRAINT "Equipment Types_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Equipment"
    ADD CONSTRAINT "Equipment_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Job Statuses"
    ADD CONSTRAINT "Job Statuses_name_key" UNIQUE (name);


ALTER TABLE ONLY "Library Makerspace"."Job Statuses"
    ADD CONSTRAINT "Job Statuses_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Jobs"
    ADD CONSTRAINT "Jobs_pkey" PRIMARY KEY (id);


ALTER TABLE ONLY "Library Makerspace"."Patrons"
    ADD CONSTRAINT "Patrons_email_key" UNIQUE (email);


ALTER TABLE ONLY "Library Makerspace"."Patrons"
    ADD CONSTRAINT "Patrons_pkey" PRIMARY KEY (id);


CREATE TRIGGER enforce_training BEFORE INSERT OR UPDATE ON "Library Makerspace"."Jobs" FOR EACH ROW EXECUTE FUNCTION "Library Makerspace".check_training();


ALTER TABLE ONLY "Library Makerspace"."Equipment Training"
    ADD CONSTRAINT "Equipment Training_equipment_id_fkey" FOREIGN KEY (equipment_id) REFERENCES "Library Makerspace"."Equipment"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Equipment Training"
    ADD CONSTRAINT "Equipment Training_patron_id_fkey" FOREIGN KEY (patron_id) REFERENCES "Library Makerspace"."Patrons"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Equipment"
    ADD CONSTRAINT "Equipment_status_id_fkey" FOREIGN KEY (status_id) REFERENCES "Library Makerspace"."Equipment Statuses"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Equipment"
    ADD CONSTRAINT "Equipment_type_id_fkey" FOREIGN KEY (type_id) REFERENCES "Library Makerspace"."Equipment Types"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Jobs"
    ADD CONSTRAINT "Jobs_equipment_id_fkey" FOREIGN KEY (equipment_id) REFERENCES "Library Makerspace"."Equipment"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Jobs"
    ADD CONSTRAINT "Jobs_patron_id_fkey" FOREIGN KEY (patron_id) REFERENCES "Library Makerspace"."Patrons"(id) ON DELETE CASCADE;


ALTER TABLE ONLY "Library Makerspace"."Jobs"
    ADD CONSTRAINT "Jobs_status_id_fkey" FOREIGN KEY (status_id) REFERENCES "Library Makerspace"."Job Statuses"(id) ON DELETE CASCADE;
